package com.leilei.dao.impl;

import com.leilei.dao.ICommodityColor;
import com.leilei.dao.ICommodityDao;
import com.leilei.dao.ICommodityImgs;
import com.leilei.dao.ICommodityVersion;
import com.leilei.entity.*;
import com.leilei.util.DBUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CommodityDaoImpl implements ICommodityDao {

    private QueryRunner queryRunner = new QueryRunner(DBUtil.getDataSource());
    private ICommodityVersion commodityVersion = new CommodityVersionImpl();
    private ICommodityColor commodityColor = new CommodityColorImpl();
    private ICommodityImgs commodityImgs = new CommodityImgsImpl();

    @Override
    public List<Commodity> selectCommodityInIndex(int type) {

        String sql = "SELECT pid, ctid, commodityName, describetest FROM commodity where ctid = ?";

        try {
            List<Commodity>  list = queryRunner.query(sql, new BeanListHandler<Commodity>(Commodity.class), type);

            for (Commodity c : list) {
                c.setCommodityColors(commodityColor.selectCommodityColorByPid(c.getPid()));
                c.setCommodityImgs(commodityImgs.selectCommodityImgsByPid(c.getPid()));
                c.setCommodityVersion(commodityVersion.selectCommodityVersionByPid(c.getPid()));
            }

            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return null;
    }

    @Override
    public List<Commodity> selectCommodityByPage(int type, int page, int pageSize) {

        String sql = "SELECT pid, ctid, commodityName, describetest FROM commodity where ctid = ? limit ?, ?";

        try {
            List<Commodity>  list = queryRunner.query(sql, new BeanListHandler<Commodity>(Commodity.class), type, (page - 1) * pageSize, pageSize);

            for (Commodity c : list) {
                c.setCommodityColors(commodityColor.selectCommodityColorByPid(c.getPid()));
                c.setCommodityImgs(commodityImgs.selectCommodityImgsByPid(c.getPid()));
                c.setCommodityVersion(commodityVersion.selectCommodityVersionByPid(c.getPid()));
            }

            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return null;
    }

    @Override
    public Commodity selectCommodityById(int pid) {

        String sql = "SELECT pid, ctid, commodityName, describetest FROM commodity where pid = ?";

        try {
            Commodity commodity = queryRunner.query(sql, new BeanHandler<Commodity>(Commodity.class), pid);

            commodity.setCommodityColors(commodityColor.selectCommodityColorByPid(commodity.getPid()));
            commodity.setCommodityImgs(commodityImgs.selectCommodityImgsByPid(commodity.getPid()));
            commodity.setCommodityVersion(commodityVersion.selectCommodityVersionByPid(commodity.getPid()));

            return commodity;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return null;
    }

    @Override
    public Commodity selectCommodityCustom(int pid, int pvid, int pcid) {

        String sql = "SELECT pid, ctid, commodityName, describetest FROM commodity where pid = ?";

        try {
            Commodity commodity = queryRunner.query(sql, new BeanHandler<Commodity>(Commodity.class), pid);

            List<CommodityVersion> commodityVersionList = new ArrayList<>();
            commodityVersionList.add(commodityVersion.getCommodityVersionByPvid(pvid));

            List<CommodityColor> commodityColorList = new ArrayList<>();
            commodityColorList.add(commodityColor.selectCommodityColorByPcid(pcid));

            List<CommodityImgs> commodityImgsList = new ArrayList<>();
            commodityImgsList.add(commodityImgs.selectCommodityCustom(pid));

            commodity.setCommodityVersion(commodityVersionList);
            commodity.setCommodityColors(commodityColorList);
            commodity.setCommodityImgs(commodityImgsList);

            return commodity;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return null;
    }

    @Override
    public List<Commodity> selectCommodityByPage(Page page, int type) {

        String sql = "SELECT pid, ctid, commodityName, describetest FROM commodity where ctid = ? limit ? , ?";

        try {
            List<Commodity>  list = queryRunner.query(sql,
                    new BeanListHandler<Commodity>(Commodity.class),
                    type, page.getStartRows(), page.getPageSize());

            for (Commodity c : list) {
                c.setCommodityColors(commodityColor.selectCommodityColorByPid(c.getPid()));
                c.setCommodityImgs(commodityImgs.selectCommodityImgsByPid(c.getPid()));
                c.setCommodityVersion(commodityVersion.selectCommodityVersionByPid(c.getPid()));
            }

            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return null;
    }

    @Override
    public int selectCountByType(int type) {

        String sql = "SELECT count(1) FROM commodity where ctid = ?";

        try {
            Number number = queryRunner.query(sql, new ScalarHandler<Number>(), type);

            return number.intValue();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return 0;
    }

    @Override
    public int updateCommodity(Commodity commodity) {

        String sql = "update commodity set commodityName = ?, describetest = ? where pid = ?";

        try {
            return queryRunner.update(sql, commodity.getCommodityName(), commodity.getDescribetest(), commodity.getPid());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public int insertCommodity(Commodity commodity) {

        String sql = "insert into commodity(ctid, commodityName, describetest) values(?, ?, ?)";
        try {

            queryRunner.update(sql, commodity.getCtid(), commodity.getCommodityName(), commodity.getDescribetest());
            int result = selectCommodityByName(commodity.getCommodityName());

            if (result > 0){

                for (CommodityVersion version : commodity.getCommodityVersion()) {
                    version.setPid(result);
                    commodityVersion.insertCommodityVersion(version);
                }

                for (CommodityColor color : commodity.getCommodityColors()) {
                    color.setPid(result);
                    commodityColor.insertCommodityColor(color);
                }

                return result;
            } else{
                return 0;
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public int selectCommodityByName(String commodityName) {

        String sql = "select pid from commodity where commodityName = ?";

        try {
            Number number = queryRunner.query(sql, new ScalarHandler<Number>(), commodityName);
            return number.intValue();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public List<Commodity> lunboCommodity() {


        String sql = "SELECT pid, ctid, commodityName, describetest from commodity where pid in (select pid from commodityimgs where isbigindex = 1)";

        try {
            List<Commodity> list = queryRunner.query(sql, new BeanListHandler<Commodity>(Commodity.class));

            for (Commodity c : list) {
                c.setCommodityImgs(commodityImgs.lunboCommodityImgs(c.getPid()));
            }

            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return null;
    }

}
